If you are a WordPress developer like I am then you have had to make WordPress related content such as related posts. In this tutorial we will create SQL queries that will give us WordPress relates posts.
WordPress Related Posts
To make this tutorial as simple as possible and as short as possible we will assume that we are working with articles (even though we can also work with any other content ).
Articles are of type post within WordPress so with the examples provided by this tutorial you will be able to make other WordPress related content of different types.
This tutorial will consist of code snippets which can be used within a theme or a plugin. Since this tutorial should be as simple as possible, we will just assume that we are working on a WordPress theme for a magazine or blog where we could make use of related posts. If you are working on a theme or a plugin then you can add the code we write inside your WordPress solution.
Defining the scope of Related Posts
Before we start writing code we have to decide the scope of our related posts so that we know how our related posts are defined. Since we want to show only related posts to an article that is being read we will have some simple terms of search.
To make the query faster we will not search for WordPress related posts by looking for the common word in the article, within the title or similar since that would take more time than by looking for the common term in a WordPress taxonomy.
We also want real related posts that make sense so we will write a query that will look for related posts which have to have at least 5 common terms in a taxonomy. To make it more understandable here is an example:
We have the taxonomy Category with terms: WordPress, PHP, Tutorial, SQL, News, Freelance, Project, HTML, CSS, Themes and Plugins. We have five articles:
- Article A, categories: WordPress, PHP, Tutorial, SQL, Themes, Plugins, HTML, Project
- Article B, categories: News, Freelance, Project, WordPress
- Article C, categories: WordPress, PHP, Tutorial, SQL, Themes, Plugins
- Article D, categories: Tutorial, HTML, Project, PHP, SQL, Freelance
- Article E, categories: News, Freelance
Our main article is Article A. Real related posts will be articles that have at least 5 common Category terms with the Article A. Those articles are:
- Article C, categories: WordPress, PHP, Tutorial, SQL, Themes, Plugins
(6 common terms with Article A)
- Article D, categories: Tutorial, HTML, Project, PHP, SQL, Freelance (5 common terms with Article A)
Understanding the Database
When writing complex queries such as this one I would recommend to write the queries by hand on a paper. When you do this you can easily change the query while you are look at the database tables to understand their relationships. Since we have one article for which we want to find the related posts we need the information how they relate between each other using taxonomies.
Tables that are used for terms and taxonomies are:
- wp_terms
- wp_term_relationships
- wp_term_taxonomy
The prefix wp_ could be different for your website. This is the default one so we will use this one.
The only table that relates to other content is wp_term_relationships since it has a column object_id which does represent the ID of the related content (this is in our case the id of the article). This table is used as a map which joins the article ID and the taxonomy term ID which can be a tag or category.
For us to make the query and look for all the content with the same taxonomy term ID, this table wp_term_relationships is all we need. If we would like to show the WordPress Related Posts only from a category, tag or something else then we would need to use the other tables also.
Creating the query
Before we create the query, create a function that will be used later on for displaying the related content where we want.
This functions requires one input and that input is the ID of the content for which we want the related posts. Global variable $wpdb will be used for querying the database.
Our query needs to do the following:
Get the IDs of articles and the number of term taxonomies that are common with our main article. Then it needs to join the ID of a term taxonomy with the ID of a term taxonomy of our main article so that we know we are working on the same taxonomies. Then we will need to list all articles except the ID of our main article since the main article can`t be also a related post to itself. We will also have to group our query results by IDs of articles so that we do not get rows with the same ID since that would mean that we could show the same related post more than once. We will also have to write into the query to list IDs of articles which have more than 4 terms that are also common to the main article.
If we name our table which will represent the list of all article by the name of t1, and the table which will represent the main article t2 (even though this is the same table, but we need to build relationships between the main article and the rest of articles), then our query will look like this:
Getting the WordPress Related Posts
Now we have our first query which will return the IDs of related posts and the number of common terms. It is time to get the data of each related post. Since we are listing the IDs of posts we could use the command IN in the query so that we get all the posts which have their ID inside the command IN.
So our query will consist of the main query and a subquery. The subquery will need to get only the IDs of the related posts and then the main query will get all the post data for each ID that was returned by the subquery. Our query will now look like this:
We now have a query that can select up to 20 related posts directly by using one query on the MySQL database. Now we need to write that inside the function ibenic_get_related_content that we have defined previously.
Using the Function
We will now see how this function can be easily used. In this example we will display all WordPress related posts at the bottom of the article content:
This is just an example of what you can do with this function. There are also other possible usages of this function such as:
- using it directly in a theme
- using it directly in a plugin
- creating a slider with related content
Additional Condition on the Query
Maybe the query we have previously defined is too simple for you? We can change the SQL query and create another one which is more complex.
Here are some examples on how to change the SQL:
Limiting the Query to a Single Taxonomy
If we want to show WordPress related posts based only on tags and not categories then we need to:
- join the additional table which contains the names of taxonomies
- adding a condition which will only include the taxonomy ( = ) or exclude taxonomies we do not want ( != )
By doing the following, our query would look like this:
Limiting the Query with Meta Data
So what if our related posts need to be additionally filtered by meta data? We will use the above changed query where we have limited it with a specific taxonomy.
Conclusion
In this tutorial we have learned how to manually write SQL queries and get WordPress related posts. We have also seen how to limit the query even further so that we can have related posts filtered by meta data or a specific taxonomy. By following this tutorial you have the knowledge to create complex queries on any content you need. You don`t have to create additional tables but only use the default WordPress tables in the database.
Did you have similar requests when building a theme or plugin? How did you handle that? Share your snippets in the comments below! Have a question for some other content or complex query? Let us know in the comments:)
Share this: